package com.sinai.mshab.server;

import java.util.Iterator;
import java.util.StringTokenizer;

import com.sinai.mshab.client.ui.Key;

public class SQLUtils {

	/**
	 * Constructs where caluses, for all additions of row/col an AND is added.
	 * 
	 * @param current_where
	 *            the current where string so far
	 * @param new_where_col
	 *            the new col to be added to the string
	 * @param new_where_val
	 *            the new val to be added to thes tring
	 * @return the updated where clause
	 */
	public static String appendWhere(String current_where,
			String new_where_col, String new_where_val) {

		new_where_val = "'" + new_where_val + "'";

		return current_where + " AND " + new_where_col + "=" + new_where_val;
	}

	/**
	 * Adds a new string value to another string seperated by commas the first
	 * value does not have a comma infront of it. As well this will put ' '
	 * around the appended item
	 * 
	 * @param current_vals
	 *            the string to which to append
	 * @param new_val
	 *            the new value being appneded to the current_col string
	 * @return the new concatinated string
	 */
	public static String appendValue(String current_vals, String new_val) {

		new_val = new_val.replace("'", "\\'");
		new_val = "'" + new_val + "'";

		if (current_vals.length() == 0) {
			return new_val;
		} else {
			return current_vals + ", " + new_val;
		}
	}

	/**
	 * Adds a new string value to another string seperated by commas the first
	 * value does not have a comma infront of it.
	 * 
	 * @param current_col
	 *            the string to which to append
	 * @param new_col
	 *            the new value being appneded to the current_col string
	 * @return the new concatinated string
	 */
	public static String appendColumn(String current_col, String new_col) {

		if (current_col.length() == 0) {
			return new_col;
		} else {
			return current_col + ", " + new_col;
		}
	}

	/**
	 * Creates a new insert statement given a table name, columns and values
	 * 
	 * @param table_name
	 *            the table into which to insert
	 * @param columns
	 *            the fields to which the information will go to
	 * @param values
	 *            the values to populate the provided fields
	 * @return the sql statement to insert the given information
	 */
	public static String construct_insert_sql(String table_name,
			String columns, String values, String where_columns,
			String where_values, Key dbEntryKey) {

		// adding the primary keys to be inserted.
		Iterator primary_keys = dbEntryKey.getKeys().keySet().iterator();
		while (primary_keys.hasNext()) {
			String key = (String) primary_keys.next();
			String val = (String) dbEntryKey.getKeys().get(key);
			columns = appendColumn(columns, key);
			values = appendValue(values, val);
		}

		// add there where columns and values
		if (where_columns.length() > 0)
			columns += ", " + where_columns;
		if (where_values.length() > 0)
			values += ", " + where_values;

		return "INSERT INTO " + table_name + " (" + columns + ") " + "VALUES ("
				+ values + ")";
	}

	/**
	 * Creates a new update statement given a table name, columns and values
	 * 
	 * @param table_name
	 *            the table into which to update
	 * @param columns
	 *            the fields to which the information will go to
	 * @param values
	 *            the values to populate the provided fields
	 * @return the sql statement to update the given information
	 */
	public static String construct_update_sql(String table_name,
			String columns, String values, String wheres, Key dbEntryKey) {

		String sql = "UPDATE " + table_name + " SET ";
		StringTokenizer cols = new StringTokenizer(columns, ",");
		StringTokenizer vals = new StringTokenizer(values, ",");

		// append the column=value clauses
		if (cols.countTokens() > 0) {
			while (cols.hasMoreTokens()) {
				String col = cols.nextToken();
				String val = vals.nextToken();
				sql += " " + col + "=" + val + ", ";
			}
			sql = sql.substring(0, sql.lastIndexOf(','));
		}

		// append the primary keys as where clauses
		sql += constructKeyWhereClause(dbEntryKey);

		sql += " " + wheres;

		return sql;
	}

	/**
	 * Creates a new select statement given a field name, table name and primary
	 * keys.
	 * 
	 * @param fieldName
	 *            the field from where to retrieve the information.
	 * @param tableName
	 *            the table where the field lives.
	 * @param dbKeys
	 *            the primary keys.
	 * @return a new select statement given a field name, table name and primary
	 *         keys.
	 */
	public static String constructSelectSQL(String fieldName, String tableName,
			Key dbKeys) {
		String sql = "SELECT " + fieldName + " FROM " + tableName;

		sql += constructKeyWhereClause(dbKeys);

		return sql;
	}

	/**
	 * Returns a where clause that appends all the keys together.
	 * 
	 * @param dbEntryKey
	 *            the primary keys.
	 * @return a where clause that appends all the keys together.
	 */
	private static String constructKeyWhereClause(Key dbEntryKey) {
		String sql = " WHERE ";

		Iterator primary_keys = dbEntryKey.getKeys().keySet().iterator();
		int round = 0;
		while (primary_keys.hasNext()) {
			String key = (String) primary_keys.next();
			String val = (String) dbEntryKey.getKeys().get(key);
			val = val.replace("'", "\'");

			if (round == 0) {
				sql += " " + key + "='" + val + "'";
			} else {
				sql += " AND " + key + "='" + val + "'";
			}
			round++;
		}

		return sql;
	}
}
